MySQL減少慢查詢的方法

常見索引類型

主鍵索引
- 它是一種特殊的唯一索引，不允許有空值。
普通索引
- 最基本的索引，它沒有任何限制。
唯一索引
- 普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。
聯合索引
- 多個列值的集合。
覆蓋索引
- 通過索引數據結構，即可直接返回數據，不需要回表
前綴索引
- char/varchar太長全部做索引存在浪費且效率差
- Blob/text類型不能整列作為索引，所以需要前綴索引
- 無法利用前綴索引完成排序

索引為什麼不可用

通過索引掃描的記錄數超過30%，變成全表掃描
聯合索引中，第一個索引列使用範圍查詢（這時用到部分索引）
聯合索引中，第一個查詢條件不是最左索引列
模糊查詢條件最左以通配符%開始
HEAP表使用HASH索引時，使用範圍檢索或者ORDER BY
多表關聯時，排序字段不屬於驅動表，無法利用索引完成排序
兩個獨立索引，其中一個用於檢索，一個用於排序（只能用到一個）

複雜SQL

複雜SQL-問題

問題：全表掃秒、臨時表、文件排序
- 解決辦法:簡化成2條SQL
  - 1. 獨立的SQL查詢汽修廠ID
  - 1. 將join表中的orgid改為直接指定
優點：原有SQL改變少，性能從3秒下降為100ms左右
缺點：100ms的耗時仍然偏高，建議拆分為多條獨立SQL

複雜SQL-修改

修改後可以看出需要掃描的行數大量減少，對應的查詢時間也大幅度下降

結果

通過索引掃描的記錄數超過30%，變成全表掃描

CREATE TABLE `table3` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col1` tinyint(3) NOT NULL DEFAULT '1',
  `col2` varchar(10) NOT NULL DEFAULT '2',
  PRIMARY KEY (`id`),
  KEY `idx_col1` (`col1`),
  KEY `idx_col2` (`col2`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

查詢

DESC SELECT * FROM table3 WHERE col1 = 1 \G

結果

DESC SELECT * FROM table3 WHERE col1 = 2 \G

結果

不回表查詢

DESC SELECT col1 FROM table3 WHERE col1 = 1 \G

Paste_Image.png

DESC SELECT col1 FROM table3 WHERE col1 = 2 \G

Paste_Image.png

函數查詢不能使用索引

DESC SELECT * FROM table3 WHERE left(col2,1) = 2 \G

Paste_Image.png

DESC SELECT * FROM table3 WHERE col2 LIKE '2%' \G

Paste_Image.png

小表可以不建索引嗎

看情況，通常最好要建索引
案例
- 用mysqlslap對只有一萬行記錄的表進行簡單壓測，一種是對該表先進性排序後讀取30條記錄，另一種是對該表堆積讀取一行記錄，分別對比有索引和沒有索引的表現。結論：
  1、排序後讀取時沒有索引時慢了約37倍時間。壓測期間出現大量的Creating sort index狀態。
  2、隨機讀取一行記錄時，沒索引時慢了約44倍時間。壓測期間出現大量的Send data狀態，有索引時，則更多的是出現Sending to client狀態。
  3、不管是小表還是大表，需要時還是加上索引吧，否則有可能它就是瓶頸

參考文檔

MySQL單表百萬數據記錄分頁性能優化
http://www.cnblogs.com/lyroge/p/3837886.html
MySQL如何利用索引優化ORDER BY排序語句
http://www.cnblogs.com/anywei/archive/ 2011/12/12/mysql.html
索引、提交頻率對InnoDB表寫入速度的影響
http://imysql.com/2014/09/24/mysql-optimization-case-how-index-and-commit-rate -affect-innodb-insert.shtml
分頁優化
http://imysql.com/2014/07/26/mysql-optimization-case-paging-optimize.shtml
MySQL 5.6查詢優化器新特性的“BUG”
http:// imysql.cn/2014/08/05/a-fake-bug-with-eq-range-index-dive-limit.shtml
MySQL開發規範之我見
http://imysql.com/2015/07/23/something -important-about-mysql-design-reference.shtml